package dao.impl;

import bean.Repertory;
import dao.RepertoryDao;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import utils.c3p0Utils;

import java.sql.SQLException;
import java.util.List;

public class RepertoryDaoImpl implements RepertoryDao {
    private static QueryRunner qr = new QueryRunner(c3p0Utils.getDataSource());
    @Override
    public int MerchSumUpdate(Repertory R) {
        String sql="UPDATE Repertory SET merchSum=? where merchId=?";
        String sq2="SELECT * FROM Repertory WHERE merchId=?";

        try {
            Repertory r=qr.query(sq2,new BeanHandler<Repertory>(Repertory.class),R.getMerchId());
            if (r.getMerchSum()<R.getMerchSum()){
                /*
                * 如果某个商品的销售数量大于仓库的存储数量，则返回商品的ID
                * */
                return R.getMerchId();
            }else {
                int sum=r.getMerchSum()-R.getMerchSum();
                qr.update(sql,sum,R.getMerchId());
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return 1;
    }

    @Override
    public List<Repertory> ReperAllFind() {
        String sql="SELECT Repertory.warehouseId,Repertory.merchId,warehousename,merchname,merchSum,warehousecap FROM Repertory,Merch,WareHouse WHERE Repertory.warehouseId=WareHouse.warehouseId AND Repertory.merchId=Merch.merchId AND isalive=1";
        String sql1="SELECT SUM(merchSum) ADDmerchSum FROM Repertory WHERE warehouseId=?";
        List<Repertory> list= null;
        try {
            list = qr.query(sql,new BeanListHandler<Repertory>(Repertory.class));
            for (int i=0;i<list.size();i++){
                Repertory r=qr.query(sql1,new BeanHandler<Repertory>(Repertory.class),list.get(i).getwarehouseId());
                int sum=list.get(i).getWarehousecap()-r.getADDmerchSum();
                list.get(i).setSurplusCapacity(sum);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public List<Repertory> nameSerch(String name) {
        String sql="SELECT Repertory.warehouseId,warehousename,merchname,merchSum,warehousecap FROM Repertory,Merch,WareHouse WHERE Repertory.warehouseId=WareHouse.warehouseId AND Repertory.merchId=Merch.merchId AND isalive=1 AND warehousename=?";
        String sql1="SELECT SUM(merchSum) ADDmerchSum FROM Repertory WHERE warehouseId=?";
        List<Repertory> R=null;
        try {
            R=qr.query(sql,new BeanListHandler<Repertory>(Repertory.class),name);
            for (int i=0;i<R.size();i++){
                Repertory r=qr.query(sql1,new BeanHandler<Repertory>(Repertory.class),R.get(i).getwarehouseId());
                int sum=R.get(i).getWarehousecap()-r.getADDmerchSum();
                R.get(i).setSurplusCapacity(sum);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return R;
    }

    @Override
    public List<Repertory> idSerch(int id) {
        String sql="SELECT Repertory.warehouseId,warehousename,merchname,merchSum,warehousecap FROM Repertory,Merch,WareHouse WHERE Repertory.warehouseId=WareHouse.warehouseId AND Repertory.merchId=Merch.merchId AND isalive=1 AND Repertory.warehouseId=?";
        String sql1="SELECT SUM(merchSum) ADDmerchSum FROM Repertory WHERE warehouseId=?";
        List<Repertory> R=null;
        try {
            R=qr.query(sql,new BeanListHandler<Repertory>(Repertory.class),id);
            for (int i=0;i<R.size();i++){
                Repertory r=qr.query(sql1,new BeanHandler<Repertory>(Repertory.class),R.get(i).getwarehouseId());
                int sum=R.get(i).getWarehousecap()-r.getADDmerchSum();
                R.get(i).setSurplusCapacity(sum);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return R;
    }
}
